package server;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;


/**
 * @author Roee
 *  *   This class responsible for the all operations with the DB */

public class mysqlConnection {

	/**
	 * Open Connection
	 * @param address - path
	 * @param user
	 * @param pass = password
	 * @param dbname
	 * @return Connection
	 * @throws SQLException
	 */
	public static Connection openConnection(String address,String user,String pass,String dbname) throws SQLException 
	{
		Connection conn = null;
		try 
		{
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception ex) {/* handle the error*/}
        try 
        {
            conn = DriverManager.getConnection("jdbc:mysql://" + address + "/" + dbname,user,pass);
            System.out.println("SQL connection succeed");
        
     	} catch (SQLException ex){ 
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            throw ex;
            }
        return conn;
   	}
	
	/**
	 * Method select - Queries the DB with select query.
	 * @param key String - Query
	 * @param amoutOfFields int - # of query fields
	 * @return String[][] - a table containing the query result
	 */
	public static String[][] select(String key,int amoutOfFields)
	{
		Statement stmt;
		ArrayList<String[]> x = new ArrayList<String[]>();
		try {	
			stmt=EchoServer.connectionToDB.createStatement();
			ResultSet rs;
			if(key.contains(";"))
			{
				rs = stmt.executeQuery(key);
			}
			else
			{
				rs = stmt.executeQuery(key+";");
			}
			while(rs.next())
	 		{	 
				String[] temp=new String[amoutOfFields];
				for(int i=0;i<amoutOfFields;i++)
				{
				temp[i]=rs.getString(i+1);
				}
				x.add(temp);
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return stringArrayListToMatrix(x);
	}
	
	/**
	 * Method stringArrayListToMatrix. Convert from ArrayList<String[]> to String[][] .
	 * @param input ArrayList<String[]>
	 * @return String[][]
	 */
	private static String[][] stringArrayListToMatrix(ArrayList<String[]> input)
	{
		Iterator<String[]> it =input.iterator();
		String temp[][]=new String[input.size()][];
		int i=0;
		while(it.hasNext())
		{
			temp[i]=it.next();
			i++;
		}
		return temp;
	}
	
	
	/**
	 * Method update - Queries the DB with update query.
	 * @param query
	 * @return result
	 */
	public static String update(String query)
	{
		Statement stmt;
		try 
		{
			stmt =EchoServer.connectionToDB.createStatement();
			Integer rs = stmt.executeUpdate(query);
			return rs.toString();
		} catch (SQLException e) {e.printStackTrace(); return "FAIL";}
		
	}
	
	
	public static String check (String query)
	{
		Statement stmt;
		try 
		{
			stmt =EchoServer.connectionToDB.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			rs.beforeFirst();
			if(rs.next()) 
			{
			   String bid=rs.getString(1);
			   return bid;
			}
			else return "NotEXIST" ; 
		} catch (SQLException e) {e.printStackTrace(); return "FAIL";}
		
	}

	
}


